using System;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Odbc;

namespace Travel.Library
{
    public class DBCaller : DBParams
    {         
        #region Constructors
       
        public DBCaller()
        {
           
        }

        ~DBCaller()
        {
            //to do list
        }

        #endregion

        #region Connection (Create - Close) + Transaction Management

        /// <summary>
        /// Using to create Connection Object,Command Object,DataAdapter Object,Transaction Obejct
        /// </summary>
        public void CreateConnection()
        {
            //Check Connection Object, Has Created ?
            if (obj_Connection == null)
            {
                //Create New Connection
                obj_Connection = new OdbcConnection(strConnection);
            }

            //Check Status Connect of Connection Object
            if (obj_Connection.State == ConnectionState.Closed)
            {
                //Open Connection
                obj_Connection.Open();
                //Create New Command Object
                obj_Command = new OdbcCommand();
                //Assign Connection Object to Command Object & Initial Transaction                
                obj_Command.Connection = obj_Connection;
                obj_Transaction = obj_Connection.BeginTransaction();
                obj_Command.Transaction = obj_Transaction;
            }

            //Check Status Of Transaction Object
            if (obj_Transaction == null || obj_Transaction.Connection == null)
            {
                //Check Transaction is using ?
                if (obj_Transaction != null && obj_Transaction.Connection == null)
                {
                    obj_Transaction.Dispose();
                    obj_Transaction = null;
                }
            }
        }

        /// <summary>
        /// Using Checking Connection is Openning ? Close Connection
        /// </summary>
        public void CloseConnection()
        {
            if (obj_Connection != null)
            {
                if (obj_Connection.State == ConnectionState.Open)
                {
                    obj_Connection.Close();
                }
            }
            obj_Connection = null;
        }

        /// <summary>
        /// Manage Tracsaction Commit - Rollback
        /// </summary>
        /// <param name="status"></param>
        public void Transaction(int status)
        {
            //Check Status of Transaction Type
            switch (status)
            {
                //Status is Commit
                case DBParams.i_Commit:
                    //Check Transaction Object
                    if (obj_Transaction != null)
                    {
                        //Check Connecttion Status
                        if (obj_Connection != null && obj_Connection.State != ConnectionState.Closed && obj_Transaction.Connection != null)
                        {
                            //Commit Transaction
                            obj_Transaction.Commit();
                        }
                        //Dispose Transaction Object
                        obj_Transaction.Dispose();
                        obj_Transaction = null;
                        CloseConnection();
                    }
                    break;
                //Status is Rollback
                case i_Rollback:
                    if (obj_Transaction != null)
                    {
                        obj_Transaction.Rollback();
                        CloseConnection();
                    }
                    break;
                default:
                    break;
            }
        }

        #endregion

        #region "Paramater Collections"
        
        /// <summary>
        /// Using Add ODBCParameter on Command Object for single query
        /// </summary>
        /// <param name="mode"></param>
        /// <param name="paramNames">Collection these name of parameter</param>
        /// <param name="paramValues">Collection these values of param</param>
        private void AddParameters(int mode, string[] paramNames, object[] paramValues)
        {
            switch (mode)
            {
                case 1:
                    obj_Command.Parameters.Clear();
                    if (paramValues != null)
                        for (int i = 0; i < paramNames.Length; i++)                           
                                obj_Command.Parameters.AddWithValue(paramNames[i], paramValues[i]);
                    break;
                case 2:
                    obj_DataAdapter.SelectCommand.Parameters.Clear();
                    if (paramValues != null)
                        for (int i = 0; i < paramNames.Length; i++)
                            obj_Command.Parameters.AddWithValue(paramNames[i], paramValues[i]);
                    break;
                default:
                    obj_Command.Parameters.Clear();
                    if (paramValues != null)
                        for (int i = 0; i < paramNames.Length; i++)
                            obj_Command.Parameters.AddWithValue(paramNames[i], paramValues[i]);
                    break;
            }
        }

        /// <summary>
        /// Clear ODBCParameter Object and Command Object
        /// </summary>
        public void ClearParameters()
        {
            if (obj_Parameters != null)
            {
                obj_Parameters.Clear();
            }           
            if (obj_Command != null)
            {
                obj_Command.Parameters.Clear();
                obj_Command.Dispose();
            }
        }

        #endregion

        #region Execute Command Text / Store Procedures
                       
        /// <summary>
        /// Using to execute a query
        /// </summary>
        /// <param name="strCommandName">Name of Command Text (SQL Query Name / Store Procedure Name</param>
        /// <param name="i_CommandType">
        /// 1. Command Text : 1
        /// 2. Store Procedure : 2
        /// </param>
        /// <param name="bParams">
        /// 1. True 
        /// 2. False
        /// </param>
        /// <returns>
        /// 1. True 
        /// 2. False
        /// </returns>
        public bool ExecuteNonQuery(string strCommandName, int i_CommandType,bool bParams)
        {
            try
            {
                //Create Connection
                CreateConnection();
                //Assign Command Text For Command Object
                obj_Command.CommandText = strCommandName;
                //Check Command Type                
                if (i_CommandType == i_CommandText)
                    obj_Command.CommandType = CommandType.Text;
                else
                    obj_Command.CommandType = CommandType.StoredProcedure;
                //Check command has paramater
                if (bParams == true)
                    AddParameters(1, paramName, paramValue);
                //Return Total Row Effects by Function
                i_RowCount = obj_Command.ExecuteNonQuery();
            }
            //If has error occur
            catch (Exception ex)
            {
                //Rollback transaction
                Transaction(i_Rollback);
            }
            //If error don`t occur
            finally
            {
                //Commit transaction
                Transaction(i_Commit);
            }
            //Check Value i_RowCount and return value of Function
            if (i_RowCount > 0)
                return true;
            else
                return false;
        }
   
        /// <summary>
        /// Using to execute a query
        /// </summary>
        /// <param name="strCommandName">Name of Command Text (SQL Query Name / Store Procedure Name</param>
        /// <param name="i_CommandType">
        /// 1. Command Text : 1
        /// 2. Store Procedure : 2
        /// </param>
        /// <param name="bParams">
        /// 1. True 
        /// 2. False
        /// </param>
        /// <returns>
        /// 1. True 
        /// 2. False
        /// </returns>
        public object ExecuteScalar(string strCommandName, int i_CommandType, bool bParams)
        {            
            try
            {
                obj_Command.CommandText = strCommandName;
                if (i_CommandType == i_CommandText)
                    obj_Command.CommandType = CommandType.Text;
                else
                    obj_Command.CommandType = CommandType.StoredProcedure;
                if(bParams == true)
                    AddParameters(1,paramName,paramValue);
                o_Retval = obj_Command.ExecuteScalar();
            }
            //If has error occur
            catch (Exception ex)
            {
                //Rollback transaction
                Transaction(i_Rollback);
            }
            //If error don`t occur
            finally
            {
                //Commit transaction
                Transaction(i_Commit);
            }
            //Return Value
            return o_Retval;
        }
        
        /// <summary>
        /// Using to execute a query
        /// </summary>
        /// <param name="strCommandName">Name of Command Text (SQL Query Name / Store Procedure Name</param>
        /// <param name="i_CommandType">
        /// 1. Command Text : 1
        /// 2. Store Procedure : 2
        /// </param>
        /// <param name="bParams">
        /// 1. True 
        /// 2. False
        /// </param>
        /// <returns>Return Data at First Column</returns>
        public IDataReader ExecuteReader(string strCommandName, int i_CommandType, bool bParams)
        {
            try
            {
                //Assign Command Text For Command Object
                obj_Command.CommandText = strCommandName;
                //Check Command Type                
                if (i_CommandType == i_CommandText)
                    obj_Command.CommandType = CommandType.Text;
                else
                    obj_Command.CommandType = CommandType.StoredProcedure;
                //Check command has paramater
                if (bParams == true)
                    AddParameters(1, paramName, paramValue);
                //Return Data On DataReader
                obj_IDataReader = (IDataReader)obj_Command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            //If has error occur
            catch (Exception ex)
            {
                //Rollback transaction
                Transaction(i_Rollback);
            }
            //If error don`t occur
            finally
            {
                //Commit transaction
                Transaction(i_Commit);
            }
            //Return Value
            return obj_IDataReader;
        }
        
        /// <summary>
        /// Using to execute a query
        /// </summary>
        /// <param name="strCommandName">Name of Command Text (SQL Query Name / Store Procedure Name</param>
        /// <param name="i_CommandType">
        /// 1. Command Text : 1
        /// 2. Store Procedure : 2
        /// </param>
        /// <param name="bParams">
        /// 1. True 
        /// 2. False
        /// </param>
        /// <returns>Set Data in DataReader</returns>
        public DataSet GetDataSet(string strCommandName, int i_CommandType, bool bParams)
        {
            try
            {
                //Assign Command Text For Command Object
                obj_Command.CommandText = strCommandName;
                //Check Command Type                
                if (i_CommandType == i_CommandText)
                    obj_Command.CommandType = CommandType.Text;
                else
                    obj_Command.CommandType = CommandType.StoredProcedure;
                //Check command has paramater
                if (bParams == true)
                    AddParameters(2,paramName,paramValue);
                //Return Data on DataSet
                obj_DataAdapter.Fill(obj_DataSet);
            }
            //If has error occur
            catch (Exception ex)
            {
                //Rollback transaction
                Transaction(i_Rollback);
            }
            //If error don`t occur
            finally
            {
                //Commit transaction
                Transaction(i_Commit);
            }
            //Return Value
            return obj_DataSet;
        }

        /// <summary>
        /// Using to execute a query
        /// </summary>
        /// <param name="strCommandName">Name of Command Text (SQL Query Name / Store Procedure Name</param>
        /// <param name="i_CommandType">
        /// 1. Command Text : 1
        /// 2. Store Procedure : 2
        /// </param>
        /// <param name="bParams">
        /// 1. True 
        /// 2. False
        /// </param>
        /// <returns>Set Data in DataTable</returns>
        public DataTable GetDataTable(string strCommandName, int i_CommandType, bool bParams)
        {
            try
            {
                //Assign Command Text For Command Object
                obj_Command.CommandText = strCommandName;
                //Check Command Type                
                if (i_CommandType == i_CommandText)
                    obj_Command.CommandType = CommandType.Text;
                else
                    obj_Command.CommandType = CommandType.StoredProcedure;
                //Check command has paramater
                if (bParams == true)
                    AddParameters(2,paramName,paramValue);
                //Fill Data to DataTable
                obj_DataAdapter.Fill(obj_DataTable);
            }
            //If has error occur
            catch (Exception ex)
            {
                //Rollback transaction
                Transaction(i_Rollback);
            }
            //If error don`t occur
            finally
            {
                //Commit transaction
                Transaction(i_Commit);
            }
            //Return Value
            return obj_DataTable;
        }

        #endregion

        #region "Execute Multi Queries"

        /// <summary>
        /// Use to revert array for fix elements
        /// </summary>
        /// <param name="QueryArray">Original Size of Array</param>
        /// <param name="NewSize">Revert to new size of Array</param>
        /// <returns>Return New Array with fix size</returns>
        private Array ResizeArray(Array QueryArray, int NewSize)
        {
            // See the type of the elements
            Type QueryArrayType = QueryArray.GetType().GetElementType();
            // Build our new array maintaining the original type
            Array NewQueryArray = Array.CreateInstance(QueryArrayType, NewSize);
            // Copy the values set to the new array
            Array.Copy(QueryArray, 0, NewQueryArray, 0, Math.Min(QueryArray.Length, NewSize));

            return NewQueryArray;
        }

        /// <summary>
        /// Use to lock current connection , wait commit to another client to execute
        /// </summary>
        /// <param name="str_LockName">Lock Name</param>
        /// <returns>True / False</returns>
        private bool LockTables(string str_LockName)
        {
            bool bStatus = false;
            // Build the connection object
            OdbcConnection obj_LockConnection = new OdbcConnection(strConnection);
            obj_LockConnection.Open();
            // Build the command object
            OdbcCommand obj_LockCommand = obj_LockConnection.CreateCommand();
            // Check Available Lock
            obj_LockCommand.CommandText = "SELECT IS_FREE_LOCK('Lock_" + str_LockName + "')";
            int nStatus = 0;
            while (!bStatus)
            {
                // Execute function IS_FREE_LOCK and see what returns
                bStatus = Convert.ToBoolean(obj_LockCommand.ExecuteScalar());
                if (bStatus)
                    break;  // Could lock the table, let's exit the cycle              
            }

            // The lock is free for us, let's lock
            try
            {
                // Execute lock query
                // In MySQL the function GET_LOCK is called by: GET_LOCK(name_of_lock, timeout seconds)
                obj_LockCommand.CommandText = "SELECT GET_LOCK('Lock_" + str_LockName + "',60)";
                nStatus = Convert.ToInt32(obj_LockCommand.ExecuteScalar());
                if (nStatus == 1)
                    bStatus = true;
                else
                    bStatus = false;
            }
            catch (OdbcException ex)
            {
                // Something bad happened, let the user know              
                bStatus = false;
            }

            // Close the connection object and return the result
            obj_LockCommand.Dispose();
            obj_LockConnection.Close();
            return bStatus;
        }
        /// <summary>
        /// Use to release current connection , wait commit to another client to execute
        /// </summary>
        /// <param name="str_LockName">Lock Name</param>
        /// <returns>True / False</returns>
        private bool ReleaseTables(string str_LockName)
        {
            // Build the connection object
            OdbcConnection obj_ReleaseConnection = new OdbcConnection(strConnection);
            obj_ReleaseConnection.Open();
            // Our var to see how things end up
            bool bStatus = false;

            // Build our command object
            OdbcCommand obj_ReleaseCommand = obj_ReleaseConnection.CreateCommand();

            // See if our tables are already loocked
            try
            {
                // Execute the release lock query
                int nStatus = 0;
                // In MySQL the function RELEASE_LOCK is called by: RELEASE_LOCK(name_of_lock)
                obj_ReleaseCommand.CommandText = "SELECT RELEASE_LOCK(" + "Lock_" + str_LockName + ")";
                if (obj_ReleaseCommand.ExecuteScalar() == System.DBNull.Value)
                {
                    bStatus = false;
                }
                else
                {
                    nStatus = Convert.ToInt32(obj_ReleaseCommand.ExecuteScalar());
                    if (nStatus == 1) bStatus = true;
                    else bStatus = false;
                }
            }
            catch (OdbcException e)
            {
                // Something bad happened, let the user know
                bStatus = false;
            }

            // Close the connection object and return the result
            obj_ReleaseCommand.Dispose();
            obj_ReleaseConnection.Close();
            return bStatus;
        }
        /// <summary>
        /// Use to Add a query to collection
        /// </summary>
        /// <param name="sQuery">Content of Query</param>
        public void AddQuery(string sQuery)
        {
            // Check how many queries in array
            if (nCommands > 0)
                // Resize the array and cast to prevent an exception throw
                QueryCollection = (string[])ResizeArray(QueryCollection, QueryCollection.Length + 1);
            // Store the new query passed
            QueryCollection[nCommands] = sQuery;
            nCommands++;
        }

        /// <summary>
        /// Execute Multi Queries in collections
        /// </summary>
        /// <returns></returns>
        public bool ExecuteQueries(int i_CommandType, string[,] paramNames, object[,] paramValues)
        {
            // Our var to see how things end up
            bool bStatus = false;
            CreateConnection();
            if (nCommands > 0)
            {
                try
                {
                    // Execute the queries in our QueryCollection array
                    for (int index = 0; index < nCommands; index++)
                    {                        
                        if (LockTables(index.ToString()))
                        {
                            if (i_CommandType == i_CommandText)
                                obj_Command.CommandType = CommandType.Text;
                            else
                                obj_Command.CommandType = CommandType.StoredProcedure;
                            if (paramValues != null)
                                for (int i = 0; i < paramNames.Length-1; i++)
                                    obj_Command.Parameters.AddWithValue(paramNames[index,i], paramValues[index,i]);
                            obj_Command.CommandText = QueryCollection[index];
                            obj_Command.ExecuteNonQuery();                            
                        }
                        // Finish executing the queries, release tables
                        ReleaseTables(index.ToString());
                    }
                    // Commit our queries to the database                           
                    Transaction(i_Commit);
                    bStatus = true;
                }
                catch (Exception ex)
                {
                    bStatus = false;
                    Transaction(i_Rollback);
                }
                finally
                {
                    Transaction(i_Rollback);
                }
            }
            return bStatus;
        }

        /// <summary>
        /// Return Back Query Collection
        /// </summary>
        public void CleanQueries()
        {
            QueryCollection.Initialize();
            QueryCollection = new string[1];
            nCommands = 0;
        }

        #endregion        
    }
}